"""
coding:utf-8
@describe:操作数据库类基类
@Time:2022/5/28 21:38
@Author:川
"""
import os
from apps.configs.settings import MYSQL_HOST, MYSQL_PORT, MYSQL_USERNAME, MYSQL_PASSWORD, MYSQL_DATABASE, MYSQL_CHARSET
import pymysql

DB_CONFIG = {
    "host": MYSQL_HOST,
    "port": MYSQL_PORT,
    "user": MYSQL_USERNAME,
    "passwd": MYSQL_PASSWORD,
    "db": MYSQL_DATABASE,
    "charset": MYSQL_CHARSET
}


class SQLManager:
    # 初始化实例方法, 如果遇到操作很多sql的,尽量不要出现多次连接, 多次关闭数据库的情况
    def __init__(self):
        self.conn = None
        self.cursor = None
        self.connect()

    # 连接数据库
    def connect(self):
        self.conn = pymysql.connect(
            host=DB_CONFIG["host"],
            port=int(DB_CONFIG["port"]),
            user=DB_CONFIG["user"],
            passwd=DB_CONFIG["passwd"],
            db=DB_CONFIG["db"],
            charset=DB_CONFIG["charset"]
        )
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def create(self, sql, args=None):
        """
        创建数据: 执行一条sql创建数据,返回新增数据id
        :param sql:
        :param args:
        :return:
        """
        try:
            self.cursor.execute(sql, args)
            self.conn.commit()
            last_id = self.cursor.lastrowid
            return last_id
        except Exception as msg:
            # 发生错误时回滚
            self.conn.rollback()
            print(msg)
        finally:
            self.close()

    def handle(self, sql, args=None):
        """
        修改或者删除数据: 执行一条sql,返回数据受影响行数
        :param sql:
        :param args:
        :return:
        """
        try:
            affected_rows = self.cursor.execute(sql, args)
            self.conn.commit()
            return affected_rows
        except Exception as msg:
            # 发生错误时回滚
            self.conn.rollback()
            print(msg)
        finally:
            self.close()

    def many_sql(self, sqls):
        """
        事务操作多个sql数据,确保数据完整性
        :param sqls: 列表 [sql1,sql2]
        :return:
        """
        try:
            affected_rows = 0
            for sql in sqls:
                affected_rows += self.cursor.execute(sql)
            self.conn.commit()
            return affected_rows
        except Exception as msg:
            # 发生错误时回滚
            self.conn.rollback()
            print(msg)
        finally:
            self.close()

    def first(self, sql, args=None):
        """
        查询单条数据
        :param sql:
        :param args:
        :return:
        """
        try:
            self.cursor.execute(sql, args)
            result = self.cursor.fetchone()
            return result
        except Exception as msg:
            print(msg)
        finally:
            self.close()

    def get(self, sql, args=None):
        """
        查询多条数据
        :param sql:
        :param args:
        :return:
        """
        try:
            self.cursor.execute(sql, args)
            result = self.cursor.fetchall()
            return result
        except Exception as msg:
            print(msg)
        finally:
            self.close()

    def close(self):
        """
        关闭数据库cursor和连接
        :return:
        """
        self.cursor.close()
        self.conn.close()
